package cn.wipace.actionlib;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

import cn.wipace.actionlib.model.UserAction;
import cn.wipace.actionlib.model.UserScore;
import cn.wipace.actionlib.util.L;
import cn.wipace.actionlib.util.NumberUtil;


/**
 * sqlite
 * version=1  创建数据库，新增表tbl_sport
 * version=2  tb2_sport表增加字段 sporttype
 * version=3  新增表tbl_user_action,tbl_user_score
 * <p>
 * Created by Administrator on 2017/4/20.
 */

public class UserDbHelper extends SQLiteOpenHelper {

    public static final String DB_NAME = "score_db";
    public static final String TABLE_NAME1 = "tbl_user_action";
    public static final String TABLE_NAME2 = "tbl_user_score";

    public static final int DATABASE_NEW_VERSION = 1;
    private static UserDbHelper instatnce = null;
    private SQLiteDatabase mDb;

    /**
     * 动作数值
     */
    public class ACTION_FIELDS {
        public final static String ID = "_id";
        public final static String USER_ID = "user_id";
        public final static String PACKAGE_NAME = "package_name";
        public final static String T0 = "t0";
        public final static String T1 = "t1";
        public final static String T2 = "t2";
        public final static String T3 = "t3";
        public final static String T4 = "t4";
        public final static String T5 = "t5";
        public final static String GAME_START_TIME = "game_start_time";
        public final static String CRAETE_TIME = "create_time";
        public final static String ACTION_ADDITION = "addtion";
    }

    /**
     * 游戏得分
     */
    public class SCORE_FIELDS {
        public final static String ID = "_id";
        public final static String USER_ID = "user_id";
        public final static String PACKAGE_NAME = "package_name";
        public final static String CRAETE_TIME = "create_time";
        public final static String MIND_FULLNESS = "mind_fullness";
        public final static String EXERCISE_ABILITY = "exercise_ability";
        public final static String STRESS_RESPONSE = "stress_response";
        public final static String COORDINATION = "coordination";
        public final static String GAME_SCORE = "game_score";
        public final static String UPLOAD_RESULT = "upload_result";
        public final static String GAME_START_TIME = "game_start_time";
        public final static String SCORE_STATUE = "score_status";
        public final static String SCORE_ADDITION = "score_addtion";
    }

    private final static String CREATE_TABLE_ACTION = "CREATE TABLE IF NOT EXISTS "
            + TABLE_NAME1 + " ( " + ACTION_FIELDS.ID + " INTEGER PRIMARY KEY AUTOINCREMENT,  "
            + ACTION_FIELDS.USER_ID + " INTEGER,"
            + ACTION_FIELDS.PACKAGE_NAME + " VARCHAR(40),"
            + ACTION_FIELDS.T0 + " NUMERIC(5,3),"
            + ACTION_FIELDS.T1 + " NUMERIC(5,3),"
            + ACTION_FIELDS.T2 + " NUMERIC(5,3),"
            + ACTION_FIELDS.T3 + " NUMERIC(5,3),"
            + ACTION_FIELDS.T4 + " NUMERIC(5,3),"
            + ACTION_FIELDS.T5 + " NUMERIC(5,3),"
            + ACTION_FIELDS.GAME_START_TIME + " INTEGER,"
            + ACTION_FIELDS.CRAETE_TIME + " INTEGER" + ")";

    private final static String CREATE_TABLE_SCORE = "CREATE TABLE IF NOT EXISTS "
            + TABLE_NAME2 + " ( " + SCORE_FIELDS.ID + " INTEGER PRIMARY KEY AUTOINCREMENT,  "
            + SCORE_FIELDS.USER_ID + " INTEGER,"
            + SCORE_FIELDS.MIND_FULLNESS + " REAL,"
            + SCORE_FIELDS.EXERCISE_ABILITY + " REAL,"
            + SCORE_FIELDS.STRESS_RESPONSE + " REAL,"
            + SCORE_FIELDS.COORDINATION + " REAL,"
            + SCORE_FIELDS.GAME_SCORE + " REAL,"
            + SCORE_FIELDS.PACKAGE_NAME + " VARCHAR(40),"
            + SCORE_FIELDS.UPLOAD_RESULT + " INTEGER DEFAULT(0),"
            + SCORE_FIELDS.GAME_START_TIME + " INTEGER,"
            + SCORE_FIELDS.CRAETE_TIME + " INTEGER" + ")";

    public UserDbHelper(Context context) {
        super(context, DB_NAME, null, DATABASE_NEW_VERSION);
    }

    public static UserDbHelper getInstance(Context context) {
        if (null == instatnce) {
            synchronized (UserDbHelper.class) {
                if (null == instatnce) {
                    instatnce = new UserDbHelper(context);
                }
            }
        }
        return instatnce;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        L.e("kerry", "sqlite==onCreate==");
        mDb = db;
        mDb.execSQL(CREATE_TABLE_ACTION);
        mDb.execSQL(CREATE_TABLE_SCORE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        L.e("kerry", "sqlite==onUpgrade==" + oldVersion + "===" + newVersion);
        /*if (oldVersion == 1 && newVersion == DATABASE_NEW_VERSION) {
            db.execSQL(CREATE_TABLE_ACTION);
            db.execSQL(CREATE_TABLE_SCORE);
        } else if (oldVersion == 2 && newVersion == DATABASE_NEW_VERSION) {
            db.execSQL(CREATE_TABLE_ACTION);
            db.execSQL(CREATE_TABLE_SCORE);
        }*/
    }

    public void insertAction(UserAction userAction) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(ACTION_FIELDS.USER_ID, userAction.getUserId());
        contentValues.put(ACTION_FIELDS.PACKAGE_NAME, userAction.getPackgeName());
        contentValues.put(ACTION_FIELDS.T0, NumberUtil.keepPrecision(userAction.getT0(), 3));
        contentValues.put(ACTION_FIELDS.T1, NumberUtil.keepPrecision(userAction.getT1(), 3));
        contentValues.put(ACTION_FIELDS.T2, NumberUtil.keepPrecision(userAction.getT2(), 3));
        contentValues.put(ACTION_FIELDS.T3, NumberUtil.keepPrecision(userAction.getT3(), 3));
        contentValues.put(ACTION_FIELDS.T4, NumberUtil.keepPrecision(userAction.getT4(), 3));
        contentValues.put(ACTION_FIELDS.T5, NumberUtil.keepPrecision(userAction.getT5(), 3));
        contentValues.put(ACTION_FIELDS.GAME_START_TIME, userAction.getGameStartTime());
        contentValues.put(ACTION_FIELDS.CRAETE_TIME, System.currentTimeMillis());
        db.insert(TABLE_NAME1, null, contentValues);
    }


    public void insertScore(UserScore userScore) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(SCORE_FIELDS.USER_ID, userScore.getUid());
        contentValues.put(SCORE_FIELDS.PACKAGE_NAME, userScore.getPackageName());
        contentValues.put(SCORE_FIELDS.MIND_FULLNESS, userScore.getMindFullness());
        contentValues.put(SCORE_FIELDS.EXERCISE_ABILITY, userScore.getExerciseAbility());
        contentValues.put(SCORE_FIELDS.STRESS_RESPONSE, userScore.getEmergencyResponse());
        contentValues.put(SCORE_FIELDS.COORDINATION, userScore.getCoordination());
        contentValues.put(SCORE_FIELDS.GAME_SCORE, userScore.getGameScore());
        contentValues.put(SCORE_FIELDS.GAME_START_TIME, userScore.getGameStartTime());
        contentValues.put(SCORE_FIELDS.UPLOAD_RESULT, userScore.getUploadResult());
        contentValues.put(SCORE_FIELDS.CRAETE_TIME, System.currentTimeMillis());
        db.insert(TABLE_NAME2, null, contentValues);
    }


    public long insertScore(ContentValues contentValues) {
        SQLiteDatabase db = getWritableDatabase();
        return db.insert(TABLE_NAME2, null, contentValues);
    }

    /**
     * 条件查询
     * select * from table_name where user_id = 1
     *
     * @return
     */
    public List<UserAction> queryAction(String sql, String[] whereargs) {
        List<UserAction> list = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, whereargs);
        while (cursor.moveToNext()) {
            UserAction action = new UserAction(cursor.getLong(0), cursor.getLong(1), cursor.getString(2), cursor.getFloat(3),
                    cursor.getFloat(4), cursor.getFloat(5), cursor.getFloat(6), cursor.getFloat(7), cursor.getFloat(8), cursor.getLong(9), cursor.getLong(10));
            list.add(action);
        }
        return list;
    }

    public List<UserAction> queryActionByStartTime(long userId, long gameStartTime) {
        List<UserAction> list = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME1 + " where " + ACTION_FIELDS.USER_ID + " = " + userId + " and " + ACTION_FIELDS.GAME_START_TIME + " = " + gameStartTime;
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            UserAction action = new UserAction(cursor.getLong(0), cursor.getLong(1), cursor.getString(2), cursor.getFloat(3),
                    cursor.getFloat(4), cursor.getFloat(5), cursor.getFloat(6), cursor.getFloat(7), cursor.getFloat(8), cursor.getLong(9), cursor.getLong(10));
            list.add(action);
        }
        return list;
    }


    /**
     * 查询userAction所有数据
     * select * from table_name where user_id = 1
     *
     * @return
     */
    public List<UserAction> queryActionAll() {
        List<UserAction> list = new ArrayList<>();
        String sql = "SELECT * FROM  " + TABLE_NAME1;
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            UserAction action = new UserAction(cursor.getLong(0), cursor.getLong(1), cursor.getString(2), cursor.getFloat(3),
                    cursor.getFloat(4), cursor.getFloat(5), cursor.getFloat(6), cursor.getFloat(7), cursor.getFloat(8), cursor.getLong(9), cursor.getLong(10));
            list.add(action);
        }
        return list;
    }

    /**
     * 查询userScore所有记录
     *
     * @return
     */
    public List<UserScore> queryScoreAll() {
        List<UserScore> list = new ArrayList<>();
        String sql = "SELECT * FROM  " + TABLE_NAME2;
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            UserScore score = new UserScore(cursor.getLong(0), cursor.getLong(1), cursor.getFloat(2),
                    cursor.getFloat(3), cursor.getFloat(4), cursor.getFloat(5), cursor.getFloat(6),
                    cursor.getString(7), cursor.getShort(8), cursor.getLong(9), cursor.getLong(10));
            list.add(score);
        }
        return list;
    }

    /**
     * 根据game_start_time 查询某用户最新10条数据
     *
     * @param userId
     * @return
     */
    public List<UserScore> queryScoreTop10(long userId) {
        List<UserScore> list = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME2 + " ORDER BY " + SCORE_FIELDS.GAME_START_TIME + " DESC LIMIT 0,3";
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            UserScore score = new UserScore(cursor.getLong(0), cursor.getLong(1), cursor.getFloat(2),
                    cursor.getFloat(3), cursor.getFloat(4), cursor.getFloat(5), cursor.getFloat(6),
                    cursor.getString(7), cursor.getShort(8), cursor.getLong(9), cursor.getLong(10));
            list.add(score);
        }
        return list;
    }

    public List<UserScore> queryScoreTop10(long userId, long gameStartTime) {
        List<UserScore> list = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME2 + " where " + SCORE_FIELDS.GAME_START_TIME + "=" + gameStartTime + " ORDER BY " + SCORE_FIELDS.GAME_START_TIME + " DESC LIMIT 0,10";
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            UserScore score = new UserScore(cursor.getLong(0), cursor.getLong(1), cursor.getFloat(2),
                    cursor.getFloat(3), cursor.getFloat(4), cursor.getFloat(5), cursor.getFloat(6),
                    cursor.getString(7), cursor.getShort(8), cursor.getLong(9), cursor.getLong(10));
            list.add(score);
        }
        return list;
    }

    public Cursor queryScoreTop3() {
        String sql = "SELECT * FROM " + TABLE_NAME2 + " ORDER BY " + SCORE_FIELDS.GAME_START_TIME + " DESC LIMIT 0,3";
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);
        return cursor;
    }

    public UserScore queryScoreLatest(long userId) {
        UserScore score = null;
        String sql = "SELECT * FROM " + TABLE_NAME2 + " ORDER BY " + SCORE_FIELDS.CRAETE_TIME + " DESC LIMIT 0,1";
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            score = new UserScore(cursor.getLong(0), cursor.getLong(1), cursor.getFloat(2),
                    cursor.getFloat(3), cursor.getFloat(4), cursor.getFloat(5), cursor.getFloat(6),
                    cursor.getString(7), cursor.getShort(8), cursor.getLong(9), cursor.getLong(10));
        }
        return score;
    }

    public UserScore queryScoreById(long scoreId) {
        UserScore score = null;
        String sql = "SELECT * FROM " + TABLE_NAME2 + " where " + SCORE_FIELDS.ID + "=" + scoreId;
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            score = new UserScore(cursor.getLong(0), cursor.getLong(1), cursor.getFloat(2),
                    cursor.getFloat(3), cursor.getFloat(4), cursor.getFloat(5), cursor.getFloat(6),
                    cursor.getString(7), cursor.getShort(8), cursor.getLong(9), cursor.getLong(10));

        }
        return score;
    }

    /**
     * 删除userAction某字段
     * <p>
     * delete from table_name where user_id = 1
     *
     * @param id
     */
    public void deleteAction(int id) {
        SQLiteDatabase db = getWritableDatabase();
        db.delete(TABLE_NAME1, "user_id = ?", new String[]{String.valueOf(id)});
    }

    public void deleteScore(int id) {
        SQLiteDatabase db = getWritableDatabase();
        db.delete(TABLE_NAME2, "user_id = ?", new String[]{String.valueOf(id)});
    }


    /**
     * 更新userAction数据库的内容
     */
    public void updateAction(ContentValues values, String whereClause, String[] whereArgs) {
        SQLiteDatabase db = getWritableDatabase();
        db.update(TABLE_NAME1, values, whereClause, whereArgs);
    }

    public void updateScore(ContentValues values, String whereClause, String[] whereArgs) {
        SQLiteDatabase db = getWritableDatabase();
        db.update(TABLE_NAME2, values, whereClause, whereArgs);
    }

    public void updateScoreGameStatue(long scoreId, short gameStatus) {
        SQLiteDatabase db = getWritableDatabase();
        String sql = "update " + TABLE_NAME2 + " set " + SCORE_FIELDS.SCORE_STATUE + " = " + gameStatus + " where " + SCORE_FIELDS.ID + " = " + scoreId;
        db.execSQL(sql);
    }

    public void close() {
        if (mDb != null) {
            mDb.close();
        }
    }
}
